package net.yb.dao.impl;

import net.yb.dao.ProductDao;
import net.yb.entity.Category;
import net.yb.entity.Product;
import net.yb.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 商品Dao实现类
 */
public class ProductDaoImpl implements ProductDao {
    @Override
    public void insert(Product product) {
        try{
            Connection conn = JdbcUtil.getConnection();
            String sql = "insert into product values(null, ?, ?, ?, ?, ?, ?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, product.getPname());
            ps.setDouble(2, product.getPrice());
            ps.setString(3, product.getUnit());
            ps.setInt(4, product.getStock());
            ps.setInt(5, product.getCategory().getCid());
            ps.setString(6, product.getInfo());
            ps.executeUpdate();
        }catch(Exception e){
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Product> selectAll() {
        List<Product> list = new ArrayList<>();
        try{
            Connection conn = JdbcUtil.getConnection();
            String sql = """    
                select 
                    p.pid, p.pname, p.price, p.unit, p.stock, p.info, 
                    c.cid, c.cname, c.info cinfo
                from product p inner join category c on p.cid = c.cid
            """;
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                Product product = new Product();
                product.setPid(rs.getInt("pid"));
                product.setPname(rs.getString("pname"));
                product.setPrice(rs.getDouble("price"));
                product.setUnit(rs.getString("unit"));
                product.setStock(rs.getInt("stock"));
                product.setInfo(rs.getString("info"));

                Category category = new Category();
                category.setCid(rs.getInt("cid"));
                category.setCname(rs.getString("cname"));
                category.setInfo(rs.getString("cinfo"));
                product.setCategory(category);

                list.add(product);
            }
        }catch(Exception e){
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public Product selectById(Integer pid) {
        try{
            Connection conn = JdbcUtil.getConnection();
            String sql = """    
                select 
                    p.pid, p.pname, p.price, p.unit, p.stock, p.info, 
                    c.cid, c.cname, c.info cinfo
                from product p inner join category c on p.cid = c.cid
                where p.pid=?
            """;
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, pid);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                Product product = new Product();
                product.setPid(rs.getInt("pid"));
                product.setPname(rs.getString("pname"));
                product.setPrice(rs.getDouble("price"));
                product.setUnit(rs.getString("unit"));
                product.setStock(rs.getInt("stock"));
                product.setInfo(rs.getString("info"));

                Category category = new Category();
                category.setCid(rs.getInt("cid"));
                category.setCname(rs.getString("cname"));
                category.setInfo(rs.getString("cinfo"));
                product.setCategory(category);
                return product;
            }
        }catch(Exception e){
            throw new RuntimeException(e);
        }
        return null;
    }

    @Override
    public void updateById(Product product) {
        try{
            Connection conn = JdbcUtil.getConnection();
            String sql = """    
                update product 
                set pname=?, price=?, unit=?, stock=?, cid=?, info=?
                where pid=?
            """;
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, product.getPname());
            ps.setDouble(2, product.getPrice());
            ps.setString(3, product.getUnit());
            ps.setInt(4, product.getStock());
            ps.setInt(5, product.getCategory().getCid());
            ps.setString(6, product.getInfo());
            ps.setInt(7, product.getPid());
            ps.executeUpdate();
        }catch(Exception e){
            throw new RuntimeException(e);
        }
    }

    @Override
    public void deleteById(int id) {
        try{
            Connection conn = JdbcUtil.getConnection();
            String sql = "delete from product where pid=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ps.executeUpdate();
        }catch(Exception e){
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Product> selectByName(String name) {
        List<Product> list = new ArrayList<>();
        try{
            Connection conn = JdbcUtil.getConnection();
            String sql = """    
                select 
                    p.pid, p.pname, p.price, p.unit, p.stock, p.info, 
                    c.cid, c.cname, c.info cinfo
                from product p inner join category c on p.cid = c.cid
                where p.pname like ?
            """;
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + name + "%");
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                Product product = new Product();
                product.setPid(rs.getInt("pid"));
                product.setPname(rs.getString("pname"));
                product.setPrice(rs.getDouble("price"));
                product.setUnit(rs.getString("unit"));
                product.setStock(rs.getInt("stock"));
                product.setInfo(rs.getString("info"));

                Category category = new Category();
                category.setCid(rs.getInt("cid"));
                category.setCname(rs.getString("cname"));
                category.setInfo(rs.getString("cinfo"));
                product.setCategory(category);

                list.add(product);
            }
        }catch(Exception e){
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public List<Product> selectByPrice(double minPrice, double maxPrice) {
        List<Product> list = new ArrayList<>();
        try{
            Connection conn = JdbcUtil.getConnection();
            String sql = """    
                select 
                    p.pid, p.pname, p.price, p.unit, p.stock, p.info, 
                    c.cid, c.cname, c.info cinfo
                from product p inner join category c on p.cid = c.cid
                where p.price between ? and ?
            """;
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setDouble(1, minPrice);
            ps.setDouble(2, maxPrice);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                Product product = new Product();
                product.setPid(rs.getInt("pid"));
                product.setPname(rs.getString("pname"));
                product.setPrice(rs.getDouble("price"));
                product.setUnit(rs.getString("unit"));
                product.setStock(rs.getInt("stock"));
                product.setInfo(rs.getString("info"));

                Category category = new Category();
                category.setCid(rs.getInt("cid"));
                category.setCname(rs.getString("cname"));
                category.setInfo(rs.getString("cinfo"));
                product.setCategory(category);

                list.add(product);
            }
        }catch(Exception e){
            throw new RuntimeException(e);
        }
        return list;
    }
}
